from collections import defaultdict

from odoo import models, fields, api
from datetime import datetime, timedelta
from zoneinfo import ZoneInfo
import calendar


class ProductOutput(models.Model):
    _name = 'sb.product.output'
    _description = 'Product Output'
    _rec_name = 'name'

    name = fields.Char('name', required=True)
    year = fields.Char('Year')  # 年
    month = fields.Char('Month')  # 月份
    # WTT长别就写W1
    display = fields.Char('Name Factory')  # 名称厂别

    in_square_meter = fields.Float('Square meter')  # 平米
    out_square_meter = fields.Float('Square meter')  # 平米

    # 平均每天平米
    in_square_meter_day = fields.Float('Avg Square meter')
    out_square_meter_day = fields.Float('Avg Square meter')

    in_avg = fields.Float('Avg')  # 均价
    out_avg = fields.Float('Avg')  # 均价

    in_subtotal = fields.Float('Subtotal(excluding tax)')  # 金额(不含税)
    out_subtotal = fields.Float('Subtotal(excluding tax)')  # 金额(不含税)

    line_ids = fields.One2many('sb.product.output.line', 'parent_id', string='Product Output Line')

    sequence = fields.Integer('Sequence', default=1)
    this_month = fields.Boolean('This Month', default=True)

    def cron_update(self, m=False, day=False):
        """
        只统计本月 成品
        出货
         - 本月出货
         - 本月累计出货
         - 本月出货合计
        入库
         - 本月入库
         - 本月累计入库
         - 本月入库合计
        """

        date_list = []
        if m and day:
            today = datetime(datetime.today().year, m, day)
            year = today.year
            start_of_month = datetime(year, m, 1)
        else:
            today = datetime.today()
            year = today.year
            start_of_month = datetime(year, today.month, 1)
        for days in range((today - start_of_month).days + 1):
            f = start_of_month + timedelta(days=days)
            from_date = (f - timedelta(hours=7)).strftime('%Y-%m-%d %H:%M:%S')
            to_date = f.strftime('%Y-%m-%d 16:59:59')
            date_list.append((from_date, to_date))
        for (from_date, to_date) in date_list:
            to_date_obj = datetime.strptime(to_date, '%Y-%m-%d %H:%M:%S')
            m = to_date_obj.month
            day = to_date_obj.day
            month = '%02d' % m
            self.search([('this_month', '=', True), ('month', '!=', month)]).update({'this_month': False})
            self.search([('this_month', '=', False), ('month', '=', month)]).update({'this_month': True})
            domain = [('year', '=', str(year)), ('month', '=', month)]
            # 本月出货 ==>  出的统计销售出货
            t_output = self.search(domain, limit=1)
            if not t_output:
                t_output = self.create({
                    'name': 'W%s' % m,
                    'display': '%s月' % m,
                    'year': year,
                    'month': month
                })
            t_output_line_id = self.env['sb.product.output.line'].search(
                [('parent_id', '=', t_output.id), ('date', '=', to_date_obj.date())], limit=1)
            cny_currency_id = self.env['res.currency'].search([('name', '=', 'CNY')], limit=1)
            if not t_output_line_id:
                t_output_line_id = t_output_line_id.create({
                    'parent_id': t_output.id,
                    'currency_id': cny_currency_id.id,
                    'date': to_date_obj.date(),
                })
            if not t_output_line_id.currency_id:
                t_output_line_id.write({'currency_id': cny_currency_id.id})
            # finished_categ = self.env.ref('sb_sale.finished_product_type').id
            category_ids = self.env['product.category'].sudo().search([('code', 'in', ['OPFP', 'CHP'])]).ids
            # 入库/出库
            t_query = f"""
                WITH
                -- 获取库存位置
                location1 AS (SELECT id FROM stock_location WHERE location_id=(SELECT id FROM stock_location WHERE barcode='WH-STOCK' AND usage='internal')),
                location2 AS (SELECT id FROM stock_location WHERE barcode='WH-STOCK' AND usage='internal'),
                location3 AS (SELECT l1.id FROM location1 l1 UNION ALL SELECT l2.id FROM location2 l2 ORDER BY id asc),
                move_line AS (
                SELECT 
                    sml.id,
                    'out' as types
                FROM stock_move_line sml
                    LEFT JOIN product_product pp ON pp.id=sml.product_id
                    LEFT JOIN product_template pt ON pt.id=pp.product_tmpl_id
                WHERE 
                    sml.state='done' AND
                    sml.date>='{from_date}' AND 
                    sml.date<='{to_date}' AND
                    sml.location_id in (SELECT id FROM location3) AND
                    sml.location_dest_id not in (SELECT id FROM location3) AND
                    pt.categ_id in {tuple(category_ids)}
                UNION ALL
                SELECT 
                    sml.id,
                    'in' as types
                FROM stock_move_line sml
                    LEFT JOIN product_product pp ON pp.id=sml.product_id
                    LEFT JOIN product_template pt ON pt.id=pp.product_tmpl_id
                WHERE 
                    sml.state='done' AND
                    sml.date>='{str(from_date)}' AND 
                    sml.date<='{str(to_date)}' AND
                    sml.location_id not in (SELECT id FROM location3) AND
                    sml.location_dest_id in (SELECT id FROM location3) AND
                    pt.categ_id in {tuple(category_ids)}
                    )
                select id,types from move_line
            """
            self.env.cr.execute(t_query)
            result_dictfetchall_data = self.env.cr.dictfetchall()
            if result_dictfetchall_data:
                in_move_line_ids = []
                out_move_line_ids = []
                for row in result_dictfetchall_data:
                    move = self.env['stock.move.line'].browse(row.get('id'))
                    if row.get('types') == 'out':
                        if move.location_dest_id.id == 17 or move.picking_type_id.barcode == 'WTT-INTERNAL-REWORK':
                            in_move_line_ids.append(row.get('id'))
                        else:
                            out_move_line_ids.append(row.get('id'))
                    else:
                        if move.location_id.usage == 'customer' and move.location_dest_id.usage == 'internal':
                            out_move_line_ids.append(row.get('id'))
                        else:
                            in_move_line_ids.append(row.get('id'))
                t_output_line_id.write({
                    'in_move_line_ids': [(6, 0, in_move_line_ids)],
                })
                t_output_line_id._compute_current_values()
                t_output_line_id.write({
                    'out_move_line_ids': [(6, 0, out_move_line_ids)],
                })
                t_output_line_id._compute_current_values()
                # 统计明细相关信息
                out_subtotal = sum(t_output.line_ids.mapped('out_amount'))
                in_subtotal = sum(t_output.line_ids.mapped('in_amount'))
                # 平米数计算
                in_square_meter = sum(t_output.line_ids.mapped('in_square_meter'))
                out_square_meter = sum(t_output.line_ids.mapped('out_square_meter'))
                in_square_meter_day = in_square_meter / day
                out_square_meter_day = out_square_meter / day
                # 总金额/总面积=平米均价
                in_avg = in_subtotal / in_square_meter if in_square_meter > 0 else 0
                out_avg = out_subtotal / out_square_meter if out_square_meter > 0 else 0
                t_output.write({
                    'in_square_meter': in_square_meter,
                    'out_square_meter': out_square_meter,
                    'in_square_meter_day': in_square_meter_day,
                    'out_square_meter_day': out_square_meter_day,
                    'out_subtotal': out_subtotal,
                    'in_subtotal': in_subtotal,
                    'in_avg': in_avg,
                    'out_avg': out_avg,
                })


class ProductOutputLine(models.Model):
    _name = 'sb.product.output.line'
    _description = 'Product Output Line'

    parent_id = fields.Many2one('sb.product.output', string='Product Output', index=True, ondelete='cascade')
    in_move_line_ids = fields.Many2many('stock.move.line',
                                        relation='in_sb_move_line',
                                        column1='output_id',
                                        column2='line_id',
                                        string='Move Line')
    out_move_line_ids = fields.Many2many('stock.move.line',
                                         relation='out_sb_move_line',
                                         column1='output_id',
                                         column2='line_id',
                                         string='Move Line')
    in_quantity = fields.Float('Quantity')
    out_quantity = fields.Float('Quantity')
    date = fields.Date('Execution Date')
    display = fields.Char('Name Factory', related='parent_id.display', store=True)  # 名称厂别
    # 币种
    currency_id = fields.Many2one('res.currency', string='Currency')
    # 板费(扣除退货后)
    board_fee = fields.Float('Board Fee')
    # 附加费
    add_fee = fields.Float('Add Fee')
    # 入库SET面积(㎡)
    in_set_area = fields.Float('In Set Area', digits='Product Price')
    # 入库金额
    in_amount = fields.Float('In Amount', digits='Product Price')
    # 出货SET面积 (㎡)
    out_set_area = fields.Float('Out Set Area', digits='Product Price')
    # 出货金额
    out_amount = fields.Float('Out Amount', digits='Product Price')
    # 平米数
    in_square_meter = fields.Float('In Square meter')
    out_square_meter = fields.Float('Out Square meter')

    types = fields.Selection([('in', 'in'), ('out', 'out')], string='type')

    def _compute_current_values(self):
        setl = self.env.ref('pcbmanufact.uom_set_length')
        setw = self.env.ref('pcbmanufact.uom_set_width')
        sheet = self.env.ref('pcbmanufact.uom_set')
        stock_move_line = self.env['stock.move.line'].sudo()
        # 库存位置
        for record in self:
            # 汇率, 转换为人民币
            cny_currency_id = record.currency_id
            in_quantity = 0
            out_quantity = 0
            add_fee = 0
            in_set_area = 0
            in_amount = 0
            out_set_area = 0
            out_amount = 0
            in_square_meter = 0
            out_square_meter = 0
            for move_line in record.in_move_line_ids:
                if move_line.location_dest_id.id == 17 or move_line.move_id.picking_type_id.barcode == 'WTT-INTERNAL-REWORK':
                    quantity = -move_line.quantity
                else:
                    quantity = move_line.quantity
                in_quantity += quantity
                cny_rate = 1
                product_id = move_line.product_id
                set_length = product_id.uom_value_ids.filtered(lambda x: x.uom_id.id == setl.id).uom_value
                set_width = product_id.uom_value_ids.filtered(lambda x: x.uom_id.id == setw.id).uom_value
                pcs_set = product_id.uom_value_ids.filtered(lambda x: x.uom_id.id == sheet.id).uom_value
                sheet_are = 0
                if set_width and set_length:
                    sheet_are = float(set_length) * float(set_width) / 1000000
                quantity_pcs = quantity / pcs_set
                set_area = sheet_are * quantity_pcs
                picking_type_id = move_line.move_id.picking_type_id
                if picking_type_id.barcode == 'WTT-DELIVERY':
                    order_line = move_line.move_id.sale_line_id
                else:
                    move_line_id = move_line
                    production_id = move_line_id.production_id
                    order_line = production_id.s_sale_order_line_ids
                    if not production_id:
                        order_line = move_line.move_id.sale_line_id
                        production_id = order_line.order_id.mrp_production_ids
                    if not production_id:
                        domain = [
                            ('active', 'in', [True, False]),  # 归档也要显示
                            ('product_id', '=', move_line.product_id.id),
                            ('lot_producing_id', '=', move_line.lot_id.id)]
                        production_id = self.env['mrp.production'].sudo().search(domain, limit=1)
                        order_line = production_id.s_sale_order_line_ids
                    if not order_line:
                        stock_lot = move_line_id.lot_id
                        order_line = stock_lot.sale_order_ids.order_line.filtered(lambda x: x.product_id.detailed_type == 'product')
                        if not order_line:
                            move_line = move_line_id.search([('lot_id', '=', stock_lot.id), ('state', '=', 'done')])
                            production_id = move_line.move_id.production_id
                            for i in move_line:
                                if i.move_id.raw_material_production_id and not i.move_id.scrapped:
                                    production_id |= i.move_id.raw_material_production_id
                            order_line = production_id.s_sale_order_line_ids
                order_line = order_line[0] if order_line else order_line
                if order_line:
                    ol = order_line.order_id.order_line
                    product = ol.filtered(lambda x: x.product_id.detailed_type == 'product')
                    total_qty = sum(product.mapped('product_uom_qty'))
                    # 费用均摊
                    service = ol.filtered(lambda x: x.product_id.detailed_type == 'service')
                    service_total = sum(service.mapped('price_subtotal'))  # 总费用
                    # 单个数量费用
                    single_ner = service_total / total_qty
                    add_fee += single_ner * quantity
                    cny_rate = cny_currency_id._get_conversion_rate(order_line.currency_id, cny_currency_id)
                in_set_area += set_area
                in_amount += order_line.price_unit * quantity * cny_rate
                in_square_meter += quantity * sheet_are / pcs_set
            for move_line in record.out_move_line_ids:
                if move_line.location_id.usage == 'customer' and move_line.location_dest_id.usage == 'internal':
                    quantity = -move_line.quantity
                else:
                    quantity = move_line.quantity
                out_quantity += quantity
                product_id = move_line.product_id
                set_length = product_id.uom_value_ids.filtered(lambda x: x.uom_id.id == setl.id).uom_value
                set_width = product_id.uom_value_ids.filtered(lambda x: x.uom_id.id == setw.id).uom_value
                pcs_set = product_id.uom_value_ids.filtered(lambda x: x.uom_id.id == sheet.id).uom_value
                sheet_are = 0
                if set_width and set_length:
                    sheet_are = float(set_length) * float(set_width) / 1000000
                quantity_pcs = quantity / pcs_set
                set_area = sheet_are * quantity_pcs
                picking_type_id = move_line.move_id.picking_type_id
                if picking_type_id.barcode == 'WTT-DELIVERY':
                    order_line = move_line.move_id.sale_line_id
                else:
                    move_line_id = self.env['stock.move.line'].sudo().search([('id', '=', move_line.id)])
                    production_id = move_line_id.production_id
                    order_line = production_id.s_sale_order_line_ids
                    if not production_id:
                        order_line = move_line.move_id.sale_line_id
                        production_id = order_line.order_id.mrp_production_ids
                    if not production_id:
                        domain = [
                            ('active', 'in', [True, False]),  # 归档也要显示
                            ('product_id', '=', move_line.product_id.id),
                            ('lot_producing_id', '=', move_line.lot_id.id)]
                        production_id = self.env['mrp.production'].sudo().search(domain)
                        order_line = production_id.s_sale_order_line_ids
                    if not order_line:
                        stock_lot = move_line_id.lot_id
                        order_line = stock_lot.sale_order_ids.order_line.filtered(
                            lambda x: x.product_id.detailed_type == 'product')
                        if not order_line:
                            move_line = stock_move_line.search([('lot_id', '=', stock_lot.id), ('state', '=', 'done')])
                            production_id = move_line.move_id.production_id
                            for i in move_line:
                                if i.move_id.raw_material_production_id and not i.move_id.scrapped:
                                    production_id |= i.move_id.raw_material_production_id
                            order_line = production_id.s_sale_order_line_ids
                order_line = order_line[0] if order_line else order_line
                cny_rate = 1
                if order_line:
                    ol = order_line.order_id.order_line
                    product = ol.filtered(lambda x: x.product_id.detailed_type == 'product')
                    total_qty = sum(product.mapped('product_uom_qty'))
                    # 费用均摊
                    service = ol.filtered(lambda x: x.product_id.detailed_type == 'service')
                    service_total = sum(service.mapped('price_subtotal'))  # 总费用
                    # 单个数量费用
                    single_ner = service_total / total_qty
                    add_fee += single_ner * quantity
                    cny_rate = cny_currency_id._get_conversion_rate(order_line.currency_id, cny_currency_id)
                out_set_area += set_area
                out_amount += order_line.price_unit * quantity * cny_rate
                out_square_meter += quantity * sheet_are / pcs_set
            record.in_quantity = in_quantity
            record.out_quantity = out_quantity
            record.add_fee = add_fee
            record.in_set_area = in_set_area
            record.in_amount = in_amount
            record.out_set_area = out_set_area
            record.out_amount = out_amount
            record.in_square_meter = in_square_meter
            record.out_square_meter = out_square_meter
